package com.amado.easyui2.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.amado.easyui2.dao.DepartmentDao;
import com.amado.easyui2.domain.Department;
import com.amado.easyui2.util.DBConnection;
import com.amado.easyui2.vo.Page;

public class DepartmetDaoOracleImpl implements DepartmentDao {

	@Override
	public int add(Department department) {
		System.out.println(department.toString());
		Connection conn = DBConnection.getConn();
		String sql = "insert into sys_dept(dept_ID ,dept_Num,dept_Name,create_Time,update_Time,create_Name,update_Name,delete_Flag)values(T_s.nextVal,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			int i = 1;
			ps = conn.prepareStatement(sql);
			ps.setString(i++, department.getDepartmentNum());
			ps.setString(i++, department.getDepartmentName());
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setString(i++, department.getCreateName());
			ps.setString(i++, department.getUpdateName());
			ps.setString(i++, department.getDeleteFlag());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(null, ps, conn);
		}
		return 0;
	}

	@Override
	public int delete(Long id) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		String sql = "update sys_dept set delete_Flag='Y' where dept_ID=?";
		try {
			int i = 1;
			ps = conn.prepareStatement(sql);
			ps.setLong(i, id);
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(null, ps, conn);
		}
		return 0;
	}

	@Override
	public int update(Department department) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
 		String sql = "update sys_dept set dept_Num=?,dept_Name=?,create_Time=?,update_Time=?,create_Name=?,update_Name=? where dept_ID=?";
		try {
			int i = 1;
			ps = conn.prepareStatement(sql);
			ps.setString(i++, department.getDepartmentNum());
			ps.setString(i++, department.getDepartmentName());
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setString(i++, department.getCreateName());
			ps.setString(i++, department.getUpdateName());
			ps.setLong(i++, department.getId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {      
			DBConnection.close(null, ps, conn);
		}
		return 0;
	}
	  @Override
	public List<Department> listAll() {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select dept_ID ,dept_Num,dept_Name,create_Time,update_Time,create_Name,update_Name from sys_dept where delete_flag='N'";
		List<Department> list = new ArrayList<Department>();
		try {
			ps = conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String departmentNum = rs.getString(i++);
				String departmentName = rs.getString(i++);
				Date createTime = rs.getTimestamp(i++);
				Date updateTime = rs.getTimestamp(i++);
				String createName = rs.getString(i++);
				String updateName = rs.getString(i++);
				list.add(new Department(id, departmentNum, departmentName, createTime, updateTime, createName,
						updateName));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		// list.forEach(e -> {
		// System.out.println(e.toString());
		// });
		return list;
	}

	@Override
	public List<Department> listAll(Page page) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM sys_dept where delete_flag='N'";
		System.out.println("sql"+sql);
		StringBuilder sqlSb = new StringBuilder();
		sqlSb.append("SELECT * FROM  (SELECT A.*, ROWNUM RN FROM (");
		sqlSb.append(sql);
		sqlSb.append(") A WHERE ROWNUM <= ? ) WHERE RN > ? ");
		System.out.println(sqlSb.toString());
		List<Department> list = new ArrayList<Department>();
		try {
			ps = conn.prepareStatement(sqlSb.toString());
			int j=1;
			ps.setInt(j++,page.getCurrentPage()*page.getRowSize());
			ps.setInt(j++,(page.getCurrentPage()-1)*page.getRowSize());
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String departmentNum = rs.getString(i++);
				String departmentName = rs.getString(i++);
				Date createTime = rs.getTimestamp(i++);
				Date updateTime = rs.getTimestamp(i++);
				String createName = rs.getString(i++);
				String updateName = rs.getString(i++);
				list.add(new Department(id,departmentNum,departmentName,createTime,updateTime,createName,updateName));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		return list;
	}

	@Override
	public int listAllCount() {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT count(1) FROM sys_dept where delete_flag='N'";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		return 0;
	}

	@Override
	public Department getByDeptName(String departmentName) {
		// TODO Auto-generated method stub
		return null;
	}
}
